package com.sxfq.loansupermarket.backsystem.modular.marketing.controller;

import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Channel;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Medium;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.SearchReservationsReportInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;
import java.util.Map;

/**
 * 预约客户信息
 *
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/reservationReport")
public class ReservationsReportController extends BaseController {
    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/marketing/report/";

    /**
     * 跳转到预约客户管理页面
     */
    @RequestMapping("")
    public String reservations(Model model) {
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "/reservationsReport.html";
    }

    @RequestMapping(value = "/getReport", method = RequestMethod.POST)
    @ResponseBody
    public List<Map<String, Object>> report(SearchReservationsReportInfo searchInfo) {
        String statusClass = searchInfo.getStatusClass();
        String starTime = searchInfo.getStarTime();
        String endTime = searchInfo.getEndTime();
        String accordingTime = searchInfo.getAccordingTime();
        String sql = "";
        String accordingTimes = "";
        if ("年".equals(accordingTime)) {
            accordingTimes = "'%Y'";
        }
        if ("月".equals(accordingTime)) {
            accordingTimes = "'%Y-%m'";
        }
        if ("日".equals(accordingTime)) {
            accordingTimes = "'%Y-%m-%d'";
        }
        if ("总计报表".equals(statusClass)) {
            sql = " select " +
                    " DATE_FORMAT(a.filing_time," + accordingTimes + ") as '时间' , " +
                    " sum(case when  a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '合计',  " +
                    " sum(case when a.`status`='预约' then 1 else 0 end) as '预约', " +
                    " sum(case when a.`status`='过期' then 1 else 0 end) as '过期', " +
                    " sum(case when a.`status`='待定' then 1 else 0 end) as '待定', " +
                    " sum(case when a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '到店', " +
                    " CONCAT(convert(sum(case when a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end)/sum(case when  a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end)*100,decimal(10,2)),'%') as '到店比率' " +
                    "  from reservations_info a where  " +
                    "  a.filing_time>='" + starTime + "' and a.filing_time<='" + endTime + "' " +
                    "  GROUP BY DATE_FORMAT(a.filing_time," + accordingTimes + ") order by a.filing_time";
        }
        if ("性别".equals(statusClass)) {
            sql = " select " +
                    " DATE_FORMAT(a.filing_time," + accordingTimes + ") as '时间' , " +
                    " sum(case when a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '合计', " +
                    " sum(case when a.customer_sex='1' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '男'," +
                    " sum(case when a.customer_sex='2' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '女'," +
                    " CONCAT(convert(sum(case when a.customer_sex='1' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end)/" +
                    " sum(case when a.customer_sex='2' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end)*100,decimal(10,2)),'%')" +
                    " as '男女比率'" +
                    " from reservations_info a where " +
                    " a.filing_time>='" + starTime + "' and a.filing_time<='" + endTime + "' " +
                    " GROUP BY DATE_FORMAT(a.filing_time," + accordingTimes + ") order by a.filing_time";
        }
        if ("年龄".equals(statusClass)) {
            sql = " select " +
                    " DATE_FORMAT(a.filing_time," + accordingTimes + ") as '时间' ," +
                    " sum(case when a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '合计', " +
                    " sum(case when a.age_range='0-9' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '0-9'," +
                    " sum(case when a.age_range='10-19' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '10-19'," +
                    " sum(case when a.age_range='20-29' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '20-29'," +
                    " sum(case when a.age_range='30-39' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '30-39'," +
                    " sum(case when a.age_range='40-49' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '40-49'," +
                    " sum(case when a.age_range='50-59' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '50-59'," +
                    " sum(case when a.age_range='60-69' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '60-69'," +
                    " sum(case when a.age_range='70-79' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '70-79'," +
                    " sum(case when a.age_range='80-89' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '80-89'," +
                    " sum(case when a.age_range='90-99' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '90-99'" +
                    " from reservations_info a where" +
                    " a.filing_time>='" + starTime + "' and a.filing_time<='" + endTime + "' " +
                    " GROUP BY DATE_FORMAT(a.filing_time," + accordingTimes + ") order by a.filing_time";
        }
        if ("渠道".equals(statusClass)) {
            sql = "select  DATE_FORMAT(a.filing_time," + accordingTimes + ") as '时间' , " +
                    " sum(case when a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '合计', ";
            String sql1 = "select * from channel a where length(pids)<5 and a.channelname!='老带新' and a.channelname!='直接上门' group by a.channelname";
            List<Channel> list1 = sqlMapper.selectList(sql1, Channel.class);
            for (int i = 0; i < list1.size(); i++) {
                if (i < list1.size() - 1) {
                    sql += "sum(case when a.one_channel='" + list1.get(i).getChannelname() + "' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '" + list1.get(i).getChannelname() + "',";
                } else {
                    sql += "sum(case when a.one_channel='" + list1.get(i).getChannelname() + "' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '" + list1.get(i).getChannelname() + "' ";
                }
            }
            sql += " from reservations_info a where" +
                    " a.filing_time>='" + starTime + "' and a.filing_time<='" + endTime + "' " +
                    " GROUP BY DATE_FORMAT(a.filing_time," + accordingTimes + ") order by a.filing_time";
        }
        if ("媒介".equals(statusClass)) {
            sql = "select  DATE_FORMAT(a.filing_time," + accordingTimes + ") as '时间' ,\n" +
                    " sum(case when  a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '合计', ";
            String sql1 = "select * from medium a where length(pids)<5 group by a.mediumname";
            List<Medium> list1 = sqlMapper.selectList(sql1, Medium.class);
            for (int i = 0; i < list1.size(); i++) {
                if (i < list1.size() - 1) {
                    sql += "sum(case when a.one_medium='" + list1.get(i).getMediumname() + "' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '" + list1.get(i).getMediumname() + "',";
                } else {
                    sql += "sum(case when a.one_medium='" + list1.get(i).getMediumname() + "' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '" + list1.get(i).getMediumname() + "' ";
                }
            }
            sql += " from reservations_info a where" +
                    " a.filing_time>='" + starTime + "' and a.filing_time<='" + endTime + "' " +
                    " GROUP BY DATE_FORMAT(a.filing_time," + accordingTimes + ") order by a.filing_time";
        }
        if ("预约项目".equals(statusClass)) {
            sql = "select  DATE_FORMAT(a.filing_time," + accordingTimes + ") as '时间' ,\n" +
                    " sum(case when  a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '合计', ";
            String sql1 = "select id,department from pay_service_info a  group by a.department";
            List<PayServiceInfo> list1 = sqlMapper.selectList(sql1, PayServiceInfo.class);
            sql += "sum(case when a.appointment_pay_service='' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '未确定',";
            for (int i = 0; i < list1.size(); i++) {
                if (i < list1.size() - 1) {
                    sql += "sum(case when a.appointment_pay_service='" + list1.get(i).getDepartment() + "' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '" + list1.get(i).getDepartment() + "',";
                } else {
                    sql += "sum(case when a.appointment_pay_service='" + list1.get(i).getDepartment() + "' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '" + list1.get(i).getDepartment() + "' ";
                }
            }
            sql += " from reservations_info a where" +
                    " a.filing_time>='" + starTime + "' and a.filing_time<='" + endTime + "' " +
                    " GROUP BY DATE_FORMAT(a.filing_time," + accordingTimes + ") order by a.filing_time";
        }
        if ("预约门诊".equals(statusClass)) {
            sql = "select  DATE_FORMAT(a.filing_time," + accordingTimes + ") as '时间' ,\n" +
                    " sum(case when  a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '合计', ";
            String sql1 = "select id, simplename, fullname from dept where 1=1  and simplename like CONCAT('%','店','%');";
            List<Dept> list1 = sqlMapper.selectList(sql1, Dept.class);
            for (int i = 0; i < list1.size(); i++) {
                if (i < list1.size() - 1) {
                    sql += "sum(case when a.outpatient_name='" + list1.get(i).getSimplename() + "' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '" + list1.get(i).getSimplename() + "',";
                } else {
                    sql += "sum(case when a.outpatient_name='" + list1.get(i).getSimplename() + "' and a.`status`='到店' and a.one_channel!='直接上门' and a.one_channel!='老带新' then 1 else 0 end) as '" + list1.get(i).getSimplename() + "' ";
                }
            }
            sql += " from reservations_info a where" +
                    " a.filing_time>='" + starTime + "' and a.filing_time<='" + endTime + "' " +
                    " GROUP BY DATE_FORMAT(a.filing_time," + accordingTimes + ") order by a.filing_time";
        }
        List<Map<String, Object>> list = sqlMapper.selectList(sql);
        return list;
    }

}